/*
 * Created on 2004-02-25
 */
package org.opencube.oms;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;

import org.apache.log4j.Logger;
import org.opencube.data.Column;
import org.opencube.data.Record;
import org.opencube.data.RecordSet;
import org.opencube.data.RecordSetMetaData;
import org.opencube.oms.meta.ComplexAttribute;
import org.opencube.oms.meta.Datatype;
import org.opencube.oms.meta.DatatypeEnumeration;
import org.opencube.oms.meta.Namespace;
import org.opencube.oms.meta.OMSMetaData;
import org.opencube.oms.meta.OMSMetaDataException;
import org.opencube.oms.meta.Scheme;
import org.opencube.oms.meta.SimpleAttribute;
import org.opencube.util.Constants;

/**
 * @author <a href="mailto:maciek@fingo.pl">FINGO - Maciej Mroczko</a>
 */
public class RecordsetConverter 
{
	private static Logger logger = Logger.getLogger( RecordsetConverter.class );
    private static String PATH_SEPARATOR = "/";

    private static SimpleDateFormat m_df = new SimpleDateFormat("dd.MM.yyyy HH:mm:ss");

    /* Converts values of type object into OMSMetaData */
    static Date convertObjectToDate(Object value) throws OMSMetaDataException, java.text.ParseException {
        Date date = null;
        if (value instanceof Date)
            date = (Date) value;
        else if (value instanceof String)
            date = m_df.parse((String) value);

        return date;
    }

    /* Converts record set into OMSMetaData */
    static OMSMetaData records2metaData(RecordSet recordSet) throws OMSMetaDataException, ParseException {
        OMSMetaData metaData = new OMSMetaData();

        HashMap namespacesMap = new HashMap();
        HashMap schemesMap = new HashMap();
        HashMap datatypesMap = new HashMap();
        HashMap datatypeEnumerationsMap = new HashMap();
        HashMap simpleAttributesMap = new HashMap();
        HashMap complexAttributesMap = new HashMap();
        Record[] records = recordSet.getRecords();
        HashMap indexes = buildIndexes(recordSet.getMetaData());

        // namespaces
        for (int i = 0; i < records.length; i++) {
            Object[] values = records[i].getValues();
            String nspId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("NSP_ID")).intValue()]);
            if (nspId != null && metaData.getNamespaceById(nspId) == null) {
                String nspParentId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("NSP_PARENT_NSP_ID")).intValue()]);
                String nspName = (String) values[((Integer) indexes.get("NSP_NAME")).intValue()];
                String nspCreater = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("NSP_CREATING_ELM_ID")).intValue()]);
                String nspModifier = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("NSP_MODIFYING_ELM_ID")).intValue()]);
                Date nspCreated = convertObjectToDate(values[((Integer) indexes.get("NSP_CREATINGDATE")).intValue()]);
                Date nspModified = convertObjectToDate(values[((Integer) indexes.get("NSP_MODIFYINGDATE")).intValue()]);
                Namespace namespace = metaData.createNamespace(nspParentId, nspId, nspName, new Date(nspCreated.getTime()), nspCreater, new Date(nspModified.getTime()), nspModifier);
                namespacesMap.put(namespace.getId(), namespace);
            }
        }

        HashMap noParents = new HashMap();
        // schemas
        for (int i = 0; i < records.length; i++) {
            Object[] values = records[i].getValues();
            addScheme(metaData, namespacesMap, schemesMap, indexes, noParents, values);
        }

        // load data types
        for (int i = 0; i < records.length; i++) {
            Object[] values = records[i].getValues();
            String datId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("DAT_ID")).intValue()]);
            if (datId != null && metaData.getDatatypeById(datId) == null) {
                String datNspId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("DAT_NSP_ID")).intValue()]);
                String datName = (String) values[((Integer) indexes.get("DAT_NAME")).intValue()];
                String datDatatype = (String) values[((Integer) indexes.get("DAT_TYPE")).intValue()];
                String valueType = (String) values[((Integer) indexes.get("DAT_VALUETYPE")).intValue()];
                // String datValuetype = (String) values[ ( (Integer)
                // indexes.get( "DAT_DAT_VALUETYPE" ) ).intValue() ];
                String datCreater = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("DAT_CREATING_ELM_ID")).intValue()]);
                String datModifier = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("DAT_MODIFYING_ELM_ID")).intValue()]);
                Date datCreated = convertObjectToDate(values[((Integer) indexes.get("DAT_CREATINGDATE")).intValue()]);
                Date datModified = convertObjectToDate(values[((Integer) indexes.get("DAT_MODIFYINGDATE")).intValue()]);
                Namespace namespace = (Namespace) namespacesMap.get(datNspId);
                boolean isUnique = false;
                if (valueType.equals("u")) {
                    isUnique = true;
                }
                Datatype datatype = namespace.createDatatype(datDatatype, datId, datName, isUnique, new Date(datCreated.getTime()), datCreater, new Date(datModified.getTime()), datModifier);
                datatypesMap.put(datatype.getId(), datatype);
            }
        }
        // load tata type enumerations
        for (int i = 0; i < records.length; i++) {
            Object[] values = records[i].getValues();
            String denId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("DEN_ID")).intValue()]);
            if (denId != null && metaData.getDatatypeEnumerationById(denId) == null) {
                String denDatId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("DEN_DAT_ID")).intValue()]);
                String denValue = (String) values[((Integer) indexes.get("DEN_VALUE")).intValue()];
                char denUse = ((String) values[((Integer) indexes.get("DEN_USE")).intValue()]).charAt(0);
                String denCreater = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("DEN_CREATING_ELM_ID")).intValue()]);
                String denModifier = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("DEN_MODIFYING_ELM_ID")).intValue()]);
                Date denCreated = convertObjectToDate(values[((Integer) indexes.get("DEN_CREATINGDATE")).intValue()]);
                Date denModified = convertObjectToDate(values[((Integer) indexes.get("DEN_MODIFYINGDATE")).intValue()]);
                Datatype datatype = (Datatype) datatypesMap.get(denDatId);
                DatatypeEnumeration datatypeEnumeration = datatype.createDatatypeEnumeration(denUse, denId, denValue, new Date(denCreated.getTime()), denCreater, new Date(denModified.getTime()), denModifier);
                datatypeEnumerationsMap.put(datatypeEnumeration.getId(), datatypeEnumeration);
            }
        }

        // simple attributes
        for (int i = 0; i < records.length; i++) {
            Object[] values = records[i].getValues();
            String satId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("SAT_ID")).intValue()]);
            if (satId != null && metaData.getSimpleAttributeById(satId) == null) {
                String satSchemeId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("SAT_SCM_ID")).intValue()]);
                String satDatId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("SAT_DAT_ID")).intValue()]);
                String satName = (String) values[((Integer) indexes.get("SAT_NAME")).intValue()];
                char satDenUse = ((String) values[((Integer) indexes.get("SAT_DEN_USE")).intValue()]).charAt(0);
                String satDefaultValue = (String) values[((Integer) indexes.get("SAT_DEFAULTVALUE")).intValue()];
                String satCreater = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("SAT_CREATING_ELM_ID")).intValue()]);
                String satModifier = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("SAT_MODIFYING_ELM_ID")).intValue()]);
                Date satCreated = convertObjectToDate(values[((Integer) indexes.get("SAT_CREATINGDATE")).intValue()]);
                Date satModified = convertObjectToDate(values[((Integer) indexes.get("SAT_MODIFYINGDATE")).intValue()]);
                Datatype datatype = (Datatype) datatypesMap.get(satDatId);
                Scheme scheme = (Scheme) schemesMap.get(satSchemeId);
                SimpleAttribute simpleAttribute = scheme.createSimpleAttribute(datatype, satDefaultValue, satDenUse, satId, satName, new Date(satCreated.getTime()), satCreater, new Date(satModified.getTime()), satModifier);
                simpleAttributesMap.put(simpleAttribute.getId(), simpleAttribute);
            }
        }

        // complex attributes
        for (int i = 0; i < records.length; i++) {
            Object[] values = records[i].getValues();
            String catId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("CAT_ID")).intValue()]);
            if (catId != null && metaData.getComplexAttributeById(catId) == null) {
                String catSourceSchemeId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("CAT_SOURCE_SCM_ID")).intValue()]);
                String catTargetSchemeId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("CAT_TARGET_SCM_ID")).intValue()]);
                String catName = (String) values[((Integer) indexes.get("CAT_NAME")).intValue()];
                char catRelationtype = ((String) values[((Integer) indexes.get("CAT_RELATIONTYPE")).intValue()]).charAt(0);
                int catMinMmultiplicity = DBHelper.bigDecimalAsInt(values[((Integer) indexes.get("CAT_MINMULTIPLICITY")).intValue()]);
                int catMaxMmultiplicity = DBHelper.bigDecimalAsInt(values[((Integer) indexes.get("CAT_MAXMULTIPLICITY")).intValue()]);
                String catCreater = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("CAT_CREATING_ELM_ID")).intValue()]);
                String catModifier = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("CAT_MODIFYING_ELM_ID")).intValue()]);
                Date catCreated = convertObjectToDate(values[((Integer) indexes.get("CAT_CREATINGDATE")).intValue()]);
                Date catModified = convertObjectToDate(values[((Integer) indexes.get("CAT_MODIFYINGDATE")).intValue()]);
                Scheme scheme = (Scheme) schemesMap.get(catSourceSchemeId);
                Scheme attributeScheme = (Scheme) schemesMap.get(catTargetSchemeId);
                ComplexAttribute complexAttribute = scheme.createComplexAttribute(attributeScheme, catMinMmultiplicity, catMaxMmultiplicity, catRelationtype, catId, catName, new Date(catCreated.getTime()), catCreater, new Date(catModified.getTime()), catModifier);
                complexAttributesMap.put(complexAttribute.getId(), complexAttribute);
            }
        }
        return metaData;
    }

    static void recordsRelations2omsStructure(RecordSet recordSet, OMSStructure structure,OMSMetaData metaData) throws OMSStructureException,OMSMetaDataException,ParseException {
        // association
        HashMap indexes = buildIndexes(recordSet.getMetaData());
        boolean assCreater = indexes.get("ASS_CREATING_ELM_ID") != null;
        boolean assModifier = indexes.get("ASS_MODIFYING_ELM_ID") != null;
        boolean assCreated = indexes.get("ASS_CREATINGDATE") != null;
        boolean assModified = indexes.get("ASS_MODIFYINGDATE") != null;
        boolean assKey = indexes.get("ASS_KEY") != null;
        boolean assSiblingOrder = indexes.get("ASS_SIBLING_ORDER") != null;

        Record[] records = recordSet.getRecords();
        HashSet addedAssIds = new HashSet(records.length);
        for (int i = 0; i < records.length; i++) {

            if (indexes.get("ASS_ID") != null) {
                Object[] values = records[i].getValues();
                String aId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("ASS_ID")).intValue()]);
                if (aId != null && !addedAssIds.contains(aId)) {
                    String aSourceId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("ASS_SOURCE_ELM_ID")).intValue()]);
                    String aTargetId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("ASS_TARGET_ELM_ID")).intValue()]);
                    String aAttributeId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("ASS_CAT_ID")).intValue()]);
                    int aOrder = assSiblingOrder ? DBHelper.bigDecimalAsInt(values[((Integer) indexes.get("ASS_SIBLING_ORDER")).intValue()]) : 0;
                    String aKey = assKey ? (String) values[((Integer) indexes.get("ASS_KEY")).intValue()] : null;
                    Date aCreated = assCreated ? convertObjectToDate(values[((Integer) indexes.get("ASS_CREATINGDATE")).intValue()]) : null;
                    String aCreater = assCreater ? DBHelper.bigDecimalAsString(values[((Integer) indexes.get("ASS_CREATING_ELM_ID")).intValue()]) : null;
                    Date aModified = assModified ? convertObjectToDate(values[((Integer) indexes.get("ASS_MODIFYINGDATE")).intValue()]) : null;
                    String aModifier = assModifier ? DBHelper.bigDecimalAsString(values[((Integer) indexes.get("ASS_MODIFYING_ELM_ID")).intValue()]) : null;
                    // append association
                    OMSElement source = (OMSElement) structure.getElementById(aSourceId);
                    OMSElement target = (OMSElement) structure.getElementById(aTargetId);
                    if (source != null && target != null) {
                        source.createAndAppendRelation(aSourceId, target, metaData.getComplexAttributeById(aAttributeId).getName(),aAttributeId, false, aId, aKey, null, aOrder, aCreater, aCreated, aModifier, aModified);
                        addedAssIds.add(aId);
                    }
                }
                // clean
                records[i].setValue(((Integer) indexes.get("ASS_ID")).intValue(), null);
                records[i].setValue(((Integer) indexes.get("ASS_SOURCE_ELM_ID")).intValue(), null);
                records[i].setValue(((Integer) indexes.get("ASS_TARGET_ELM_ID")).intValue(), null);
                records[i].setValue(((Integer) indexes.get("ASS_CAT_ID")).intValue(), null);
            }
        }
    }

    /* Converts the record set into OMSStructure */
    static OMSStructure records2omsStructure(RecordSet recordSet, OMSMetaData metaData) throws OMSMetaDataException, OMSStructureException, ParseException {
        Record[] records = recordSet.getRecords();
        logger.debug("Start converting record set to oms structure");
        logger.debug("Recordset size: " + records.length);
        HashMap addedElements = new HashMap(records.length);
        HashSet addedCmpIds = new HashSet(records.length);
        HashSet addedAssIds = new HashSet(records.length);
        HashSet addedSvlIds = new HashSet(records.length);
        HashSet addedCvlIds = new HashSet(records.length);
        HashSet addedBvlIds = new HashSet(records.length);
        HashSet addedUvlIds = new HashSet(records.length);
        HashMap<String, Integer> indexes = buildIndexes( recordSet.getMetaData() );

        // element indexes

        // composition
        boolean cmpCreater = indexes.get("CMP_CREATING_ELM_ID") != null;
        boolean cmpModifier = indexes.get("CMP_MODIFYING_ELM_ID") != null;
        boolean cmpCreated = indexes.get("CMP_CREATINGDATE") != null;
        boolean cmpModified = indexes.get("CMP_MODIFYINGDATE") != null;
        boolean cmpKey = indexes.get("CMP_KEY") != null;
        boolean cmpSiblingOrder = indexes.get("CMP_SIBLING_ORDER") != null;
        // association
        boolean assCreater = indexes.get("ASS_CREATING_ELM_ID") != null;
        boolean assModifier = indexes.get("ASS_MODIFYING_ELM_ID") != null;
        boolean assCreated = indexes.get("ASS_CREATINGDATE") != null;
        boolean assModified = indexes.get("ASS_MODIFYINGDATE") != null;
        boolean assKey = indexes.get("ASS_KEY") != null;
        boolean assSiblingOrder = indexes.get("ASS_SIBLING_ORDER") != null;
        // element
        boolean elmCreater = indexes.get("ELM_CREATING_ELM_ID") != null;
        boolean elmModifier = indexes.get("ELM_MODIFYING_ELM_ID") != null;
        boolean elmCreated = indexes.get("ELM_CREATINGDATE") != null;
        boolean elmModified = indexes.get("ELM_MODIFYINGDATE") != null;
        // simplevalue
        boolean svlCreater = indexes.get("SVL_CREATING_ELM_ID") != null;
        boolean svlModifier = indexes.get("SVL_MODIFYING_ELM_ID") != null;
        boolean svlCreated = indexes.get("SVL_CREATINGDATE") != null;
        boolean svlModified = indexes.get("SVL_MODIFYINGDATE") != null;
        // uniquevalue
        boolean uvlCreater = indexes.get("UVL_CREATING_ELM_ID") != null;
        boolean uvlModifier = indexes.get("UVL_MODIFYING_ELM_ID") != null;
        boolean uvlCreated = indexes.get("UVL_CREATINGDATE") != null;
        boolean uvlModified = indexes.get("UVL_MODIFYINGDATE") != null;
        // clobvalue
        boolean cvlCreater = indexes.get("CVL_CREATING_ELM_ID") != null;
        boolean cvlModifier = indexes.get("CVL_MODIFYING_ELM_ID") != null;
        boolean cvlCreated = indexes.get("CVL_CREATINGDATE") != null;
        boolean cvlModified = indexes.get("CVL_MODIFYINGDATE") != null;
        // blobvalue
        boolean bvlCreater = indexes.get("BVL_CREATING_ELM_ID") != null;
        boolean bvlModifier = indexes.get("BVL_MODIFYING_ELM_ID") != null;
        boolean bvlCreated = indexes.get("BVL_CREATINGDATE") != null;
        boolean bvlModified = indexes.get("BVL_MODIFYINGDATE") != null;

        OMSStructure structure = new OMSStructure(records.length);
        logger.debug("Start elements and values convertion");
        // fetch elements
        for (int i = 0; i < records.length; i++) {
            if (i != 0 && i % 5000 == 0) {
                logger.debug(i + " of " + records.length + " records converted into elements and values");
            }
            Object[] values = records[i].getValues();

            // element
            String eId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("ELM_ID")).intValue()]);
            OMSElement element = (OMSElement) addedElements.get(eId);
            if (element == null) {
                String eDisplayName = (String) values[((Integer) indexes.get("ELM_DISPLAYNAME")).intValue()];
                String eBookmark = (String) values[((Integer) indexes.get("ELM_BOOKMARK")).intValue()];
                String eSchemeId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("ELM_SCM_ID")).intValue()]);
                // int eValid = DBHelper.bigDecimalAsInt( values[ ( (Integer)
                // indexes.get( "ELM_VALID" ) ).intValue() ] );

                Date eCreated = elmCreated ? convertObjectToDate(values[((Integer) indexes.get("ELM_CREATINGDATE")).intValue()]) : null;
                String eCreater = elmCreater ? DBHelper.bigDecimalAsString(values[((Integer) indexes.get("ELM_CREATING_ELM_ID")).intValue()]) : null;
                Date eModified = elmModified ? convertObjectToDate(values[((Integer) indexes.get("ELM_MODIFYINGDATE")).intValue()]) : null;
                String eModifier = elmModifier ? DBHelper.bigDecimalAsString(values[((Integer) indexes.get("ELM_MODIFYING_ELM_ID")).intValue()]) : null;
                // append the element
                element = structure.createAndAppendElement(null, metaData.getSchemeById(eSchemeId).getNamespace().getPath(), metaData.getSchemeById(eSchemeId).getName(), eId, eDisplayName, eBookmark, eCreated, eCreater, eModified, eModifier);

                addedElements.put(eId, element);
            }
            // clean element
            records[i].setValue(((Integer) indexes.get("ELM_ID")).intValue(), null);
            records[i].setValue(((Integer) indexes.get("ELM_DISPLAYNAME")).intValue(), null);
            records[i].setValue(((Integer) indexes.get("ELM_BOOKMARK")).intValue(), null);
            records[i].setValue(((Integer) indexes.get("ELM_SCM_ID")).intValue(), null);
            // simple value
            if (indexes.get("SVL_ID") != null) {
                String svId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("SVL_ID")).intValue()]);
                if (svId != null && !addedSvlIds.contains(svId)) {
                    Object svValue = (String) values[((Integer) indexes.get("SVL_VALUE")).intValue()];
                    // String svValueType = (String) values[ ( (Integer)
                    // indexes.get( "SVL_VALUETYPE" ) ).intValue() ];
                    String svElmId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("SVL_ELM_ID")).intValue()]);
                    // String svDatId = DBHelper.bigDecimalAsString( values[ (
                    // (Integer) indexes.get( "SVL_DAT_ID" ) ).intValue() ] );
                    String svDenId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("SVL_DEN_ID")).intValue()]);
                    String svSatId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("SVL_SAT_ID")).intValue()]);

                    Date svCreated = svlCreated ? convertObjectToDate(values[((Integer) indexes.get("SVL_CREATINGDATE")).intValue()]) : null;
                    String svCreater = svlCreater ? DBHelper.bigDecimalAsString(values[((Integer) indexes.get("SVL_CREATING_ELM_ID")).intValue()]) : null;
                    Date svModified = svlModified ? convertObjectToDate(values[((Integer) indexes.get("SVL_MODIFYINGDATE")).intValue()]) : null;
                    String svModifier = svlModifier ? DBHelper.bigDecimalAsString(values[((Integer) indexes.get("SVL_MODIFYING_ELM_ID")).intValue()]) : null;

                    // append the simple value
                    element.createAndAppendValue(metaData.getDatatypeEnumerationById(svDenId).getName(), metaData.getSimpleAttributeById(svSatId).getName(), svId, svValue, svCreated, svCreater, svModified, svModifier);
                    addedSvlIds.add(svId);
                }
                // clean
                records[i].setValue(((Integer) indexes.get("SVL_ID")).intValue(), null);
                records[i].setValue(((Integer) indexes.get("SVL_VALUE")).intValue(), null);
                records[i].setValue(((Integer) indexes.get("SVL_ELM_ID")).intValue(), null);
                records[i].setValue(((Integer) indexes.get("SVL_DEN_ID")).intValue(), null);
                records[i].setValue(((Integer) indexes.get("SVL_SAT_ID")).intValue(), null);
            }
            // unique value
            if (indexes.get("UVL_ID") != null) {
                String uvId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("UVL_ID")).intValue()]);
                if (uvId != null && !addedUvlIds.contains(uvId)) {
                    Object uvValue = (String) values[((Integer) indexes.get("UVL_VALUE")).intValue()];
                    // String uvValueType = (String) values[ ( (Integer)
                    // indexes.get( "UVL_VALUETYPE" ) ).intValue() ];
                    String uvElmId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("UVL_ELM_ID")).intValue()]);
                    // String uvDatId = DBHelper.bigDecimalAsString( values[ (
                    // (Integer) indexes.get( "UVL_DAT_ID" ) ).intValue() ] );
                    String uvDenId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("UVL_DEN_ID")).intValue()]);
                    String uvSatId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("UVL_SAT_ID")).intValue()]);
                    Date uvCreated = uvlCreated ? convertObjectToDate(values[((Integer) indexes.get("UVL_CREATINGDATE")).intValue()]) : null;
                    String uvCreater = uvlCreater ? DBHelper.bigDecimalAsString(values[((Integer) indexes.get("UVL_CREATING_ELM_ID")).intValue()]) : null;
                    Date uvModified = uvlModified ? convertObjectToDate(values[((Integer) indexes.get("UVL_MODIFYINGDATE")).intValue()]) : null;
                    String uvModifier = uvlModifier ? DBHelper.bigDecimalAsString(values[((Integer) indexes.get("UVL_MODIFYING_ELM_ID")).intValue()]) : null;
                    // append the unique value
                    element.createAndAppendValue(metaData.getDatatypeEnumerationById(uvDenId).getName(), metaData.getSimpleAttributeById(uvSatId).getName(), uvId, uvValue, uvCreated, uvCreater, uvModified, uvModifier);
                    addedUvlIds.add(uvId);
                }
                // clean
                records[i].setValue(((Integer) indexes.get("UVL_ID")).intValue(), null);
                records[i].setValue(((Integer) indexes.get("UVL_VALUE")).intValue(), null);
                records[i].setValue(((Integer) indexes.get("UVL_ELM_ID")).intValue(), null);
                records[i].setValue(((Integer) indexes.get("UVL_DEN_ID")).intValue(), null);
                records[i].setValue(((Integer) indexes.get("UVL_SAT_ID")).intValue(), null);
            }

            // clob value
            if (indexes.get("CVL_ID") != null) {
                String cvId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("CVL_ID")).intValue()]);
                if (cvId != null && !addedCvlIds.contains(cvId)) {
                    Object cvValue = values[((Integer) indexes.get("CVL_VALUE")).intValue()];
                    // String cvValueType = (String) values[ ( (Integer)
                    // indexes.get( "CVL_VALUETYPE" ) ).intValue() ];
                    String cvElmId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("CVL_ELM_ID")).intValue()]);
                    // String cvDatId = DBHelper.bigDecimalAsString( values[ (
                    // (Integer) indexes.get( "CVL_DAT_ID" ) ).intValue() ] );
                    String cvDenId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("CVL_DEN_ID")).intValue()]);
                    String cvSatId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("CVL_SAT_ID")).intValue()]);
                    Date cvCreated = cvlCreated ? convertObjectToDate(values[((Integer) indexes.get("CVL_CREATINGDATE")).intValue()]) : null;
                    String cvCreater = cvlCreater ? DBHelper.bigDecimalAsString(values[((Integer) indexes.get("CVL_CREATING_ELM_ID")).intValue()]) : null;
                    Date cvModified = cvlModified ? convertObjectToDate(values[((Integer) indexes.get("CVL_MODIFYINGDATE")).intValue()]) : null;
                    String cvModifier = cvlModifier ? DBHelper.bigDecimalAsString(values[((Integer) indexes.get("CVL_MODIFYING_ELM_ID")).intValue()]) : null;
                    // append the clob value
                    element.createAndAppendValue(metaData.getDatatypeEnumerationById(cvDenId).getName(), metaData.getSimpleAttributeById(cvSatId).getName(), cvId, cvValue, cvCreated, cvCreater, cvModified, cvModifier);
                    addedCvlIds.add(cvId);
                }
                // clean
                records[i].setValue(((Integer) indexes.get("CVL_ID")).intValue(), null);
                records[i].setValue(((Integer) indexes.get("CVL_VALUE")).intValue(), null);
                records[i].setValue(((Integer) indexes.get("CVL_ELM_ID")).intValue(), null);
                records[i].setValue(((Integer) indexes.get("CVL_DEN_ID")).intValue(), null);
                records[i].setValue(((Integer) indexes.get("CVL_SAT_ID")).intValue(), null);
            }

            // blob value
            if (indexes.get("BVL_ID") != null) {
                String bvId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("BVL_ID")).intValue()]);
                if (bvId != null && !addedBvlIds.contains(bvId)) {
                    Object bvValue = (byte[]) values[((Integer) indexes.get("BVL_VALUE")).intValue()];
                    // String bvValueType = (String) values[ ( (Integer)
                    // indexes.get( "BVL_VALUETYPE" ) ).intValue() ];
                    String bvElmId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("BVL_ELM_ID")).intValue()]);
                    // String bvDatId = DBHelper.bigDecimalAsString( values[ (
                    // (Integer) indexes.get( "BVL_DAT_ID" ) ).intValue() ] );
                    String bvDenId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("BVL_DEN_ID")).intValue()]);
                    String bvSatId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("BVL_SAT_ID")).intValue()]);
                    Date bvCreated = bvlCreated ? convertObjectToDate(values[((Integer) indexes.get("BVL_CREATINGDATE")).intValue()]) : null;
                    String bvCreater = bvlCreater ? DBHelper.bigDecimalAsString(values[((Integer) indexes.get("BVL_CREATING_ELM_ID")).intValue()]) : null;
                    Date bvModified = bvlModified ? convertObjectToDate(values[((Integer) indexes.get("BVL_MODIFYINGDATE")).intValue()]) : null;
                    String bvModifier = bvlModifier ? DBHelper.bigDecimalAsString(values[((Integer) indexes.get("BVL_MODIFYING_ELM_ID")).intValue()]) : null;
                    // append the blob value
                    element.createAndAppendValue(metaData.getDatatypeEnumerationById(bvDenId).getName(), metaData.getSimpleAttributeById(bvSatId).getName(), bvId, bvValue, bvCreated, bvCreater, bvModified, bvModifier);
                    addedBvlIds.add(bvId);
                }
                // clean
                records[i].setValue(((Integer) indexes.get("BVL_ID")).intValue(), null);
                records[i].setValue(((Integer) indexes.get("BVL_VALUE")).intValue(), null);
                records[i].setValue(((Integer) indexes.get("BVL_ELM_ID")).intValue(), null);
                records[i].setValue(((Integer) indexes.get("BVL_DEN_ID")).intValue(), null);
                records[i].setValue(((Integer) indexes.get("BVL_SAT_ID")).intValue(), null);
            }
        }
        logger.debug("Elements and values converted");
        logger.debug("Start relations convertion");
        // fetch relations
        for (int i = 0; i < records.length; i++) {
            if (i != 0 && i % 5000 == 0) {
                logger.debug(i + " of " + records.length + " records converted into relations");
            }
            Object[] values = records[i].getValues();
            if (indexes.get("CMP_ID") != null) {
                String cId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("CMP_ID")).intValue()]);
                if (cId != null && !addedCmpIds.contains(cId)) {
                    String cTargetId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("CMP_TARGET_ELM_ID")).intValue()]);
                    String cSourceId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("CMP_SOURCE_ELM_ID")).intValue()]);
                    String cAttributeId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("CMP_CAT_ID")).intValue()]);
                    int cOrder = cmpSiblingOrder ? DBHelper.bigDecimalAsInt(values[((Integer) indexes.get("CMP_SIBLING_ORDER")).intValue()]) : 0;
                    String cKey = cmpKey ? (String) values[((Integer) indexes.get("CMP_KEY")).intValue()] : null;
                    String cPath = (String) values[((Integer) indexes.get("CMP_PATH")).intValue()];
                    Date cCreated = cmpCreated ? convertObjectToDate(values[((Integer) indexes.get("CMP_CREATINGDATE")).intValue()]) : null;
                    String cCreater = cmpCreater ? DBHelper.bigDecimalAsString(values[((Integer) indexes.get("CMP_CREATING_ELM_ID")).intValue()]) : null;
                    Date cModified = cmpModified ? convertObjectToDate(values[((Integer) indexes.get("CMP_MODIFYINGDATE")).intValue()]) : null;
                    String cModifier = cmpModifier ? DBHelper.bigDecimalAsString(values[((Integer) indexes.get("CMP_MODIFYING_ELM_ID")).intValue()]) : null;
                    // append composition
                    OMSElement source = (OMSElement) structure.getElementById(cSourceId);
                    OMSElement target = (OMSElement) structure.getElementById(cTargetId);
                    target.setPath(cPath);
                    // String catName = null;
                    // boolean isComposition = false;
                    ComplexAttribute cat = metaData.getComplexAttributeById(cAttributeId);
                    if (source == null) {
                        source = structure.getRootElement();
                    }
                    String catName;
                    boolean isComposition;
                    if (cSourceId == null) {
                        catName = OMSStructure.OBJECTS_ATTRIBUTE;
                        isComposition = true;
                    } else {
                        catName = cat.getName();
                        isComposition = cat.isComposition();
                    }
                    source.createAndAppendRelation(cSourceId, target, catName,cAttributeId, isComposition, cId, cKey, null, cOrder, cCreater, cCreated, cModifier, cModified);
                    addedCmpIds.add(cId);
                }
                // clean
                records[i].setValue(((Integer) indexes.get("CMP_ID")).intValue(), null);
                records[i].setValue(((Integer) indexes.get("CMP_TARGET_ELM_ID")).intValue(), null);
                records[i].setValue(((Integer) indexes.get("CMP_SOURCE_ELM_ID")).intValue(), null);
                records[i].setValue(((Integer) indexes.get("CMP_CAT_ID")).intValue(), null);
            }

            if (indexes.get("ASS_ID") != null) {
                String aId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("ASS_ID")).intValue()]);
                if (aId != null && !addedAssIds.contains(aId)) {
                    String aSourceId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("ASS_SOURCE_ELM_ID")).intValue()]);
                    String aTargetId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("ASS_TARGET_ELM_ID")).intValue()]);
                    String aAttributeId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("ASS_CAT_ID")).intValue()]);
                    int aOrder = assSiblingOrder ? DBHelper.bigDecimalAsInt(values[((Integer) indexes.get("ASS_SIBLING_ORDER")).intValue()]) : 0;
                    String aKey = assKey ? (String) values[((Integer) indexes.get("ASS_KEY")).intValue()] : null;
                    Date aCreated = assCreated ? convertObjectToDate(values[((Integer) indexes.get("ASS_CREATINGDATE")).intValue()]) : null;
                    String aCreater = assCreater ? DBHelper.bigDecimalAsString(values[((Integer) indexes.get("ASS_CREATING_ELM_ID")).intValue()]) : null;
                    Date aModified = assModified ? convertObjectToDate(values[((Integer) indexes.get("ASS_MODIFYINGDATE")).intValue()]) : null;
                    String aModifier = assModifier ? DBHelper.bigDecimalAsString(values[((Integer) indexes.get("ASS_MODIFYING_ELM_ID")).intValue()]) : null;
                    // append association
                    OMSElement source = (OMSElement) structure.getElementById(aSourceId);
                    OMSElement target = (OMSElement) structure.getElementById(aTargetId);
                    if (source != null && target != null) {
                        source.createAndAppendRelation(aSourceId, target, metaData.getComplexAttributeById(aAttributeId).getName(),aAttributeId, false, aId, aKey, null, aOrder, aCreater, aCreated, aModifier, aModified);
                        addedAssIds.add(aId);
                    }
                }
                // clean
                records[i].setValue(((Integer) indexes.get("ASS_ID")).intValue(), null);
                records[i].setValue(((Integer) indexes.get("ASS_SOURCE_ELM_ID")).intValue(), null);
                records[i].setValue(((Integer) indexes.get("ASS_TARGET_ELM_ID")).intValue(), null);
                records[i].setValue(((Integer) indexes.get("ASS_CAT_ID")).intValue(), null);
            }
        }
        logger.debug("Relations converted");
        logger.debug("Recordset converted into oms structure");
        return structure;
    }

    private static void addScheme(OMSMetaData metaData, HashMap namespacesMap, HashMap schemesMap, HashMap indexes, HashMap noParents, Object[] values) throws OMSMetaDataException, ParseException {
        String scmId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("SCM_ID")).intValue()]);
        if (scmId != null && metaData.getSchemeById(scmId) == null) {
            String scmName = (String) values[((Integer) indexes.get("SCM_NAME")).intValue()];
            String scmNspId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("SCM_NSP_ID")).intValue()]);
            String scmParentScmId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("SCM_PARENT_SCM_ID")).intValue()]);
            String scmCreater = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("SCM_CREATING_ELM_ID")).intValue()]);
            String scmModifier = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("SCM_MODIFYING_ELM_ID")).intValue()]);
            boolean scmAbstract = false;
            try {
                scmAbstract = Integer.parseInt((String) values[((Integer) indexes.get("SCM_ABSTRACT")).intValue()]) > 0;
            } catch (Exception e) {

                e.printStackTrace();
            }
            Date scmCreated = convertObjectToDate(values[((Integer) indexes.get("SCM_CREATINGDATE")).intValue()]);
            Date scmModified = convertObjectToDate(values[((Integer) indexes.get("SCM_MODIFYINGDATE")).intValue()]);
            Namespace namespace = (Namespace) namespacesMap.get(scmNspId);
            Scheme parentScheme = (scmParentScmId == null || Constants.STR_ZERO.equals(scmParentScmId)) ? null : metaData.getSchemeById(scmParentScmId);
            if (parentScheme != null || (scmParentScmId == null || Constants.STR_ZERO.equals(scmParentScmId))) {
                Scheme scheme = namespace.createScheme(parentScheme, scmId, scmName, scmAbstract, new Date(scmCreated.getTime()), scmCreater, new Date(scmModified.getTime()), scmModifier);
                schemesMap.put(scheme.getId(), scheme);
                ArrayList children = (ArrayList) noParents.get(scmId);
                if (children != null) {
                    for (Iterator child = children.iterator(); child.hasNext();) {
                        Object[] record = (Object[]) child.next();
                        addScheme(metaData, namespacesMap, schemesMap, indexes, noParents, record);
                    }
                }
            } else {
                ArrayList children = (ArrayList) noParents.get(scmParentScmId);
                if (children == null) {
                    children = new ArrayList();
                }
                children.add(values);
                noParents.put(scmParentScmId, children);
            }
        }
        
    }

    /* Converts the record set into OMSRights[] */
    static OMSRights[] records2omsRights(RecordSet recordSet, OMSElement container) throws OMSMetaDataException, ParseException {
        OMSRights[] rights = null;
        try {
            rights = records2omsRights(recordSet, new OMSElement[] { container });
        } catch (OMSStructureException ex) {
            ; // cannot happen for single container
        }
        return rights;
        /*
         * TODO - remove it Record[] records = recordSet.getRecords(); HashMap
         * indexes = buildIndexes( recordSet.getMetaData() ); OMSStructure
         * structure = container.getOMSStructure(); ArrayList rightsList = new
         * ArrayList(); for( int i = 0; i < records.length; i++ ) { Object[]
         * values = records[ i ].getValues(); // rights owner String oId =
         * DBHelper.bigDecimalAsString( values[ ( (Integer) indexes.get(
         * "AUT_SOURCE_ELM_ID" ) ).intValue() ] ); OMSElement grantedTo =
         * structure.getElementById( oId ); if( grantedTo == null ) {
         * //continue; // TODO: really? } // rights id OMSRights rights = new
         * OMSRights( container, oId, ( (String) values[ ( (Integer)
         * indexes.get( "AUT_READ" ) ).intValue() ] ).charAt( 0 ) == '1', (
         * (String) values[ ( (Integer) indexes.get( "AUT_WRITE" ) ).intValue() ]
         * ).charAt( 0 ) == '1', ( (String) values[ ( (Integer) indexes.get(
         * "AUT_COMPOSE" ) ).intValue() ] ).charAt( 0 ) == '1', ( (String)
         * values[ ( (Integer) indexes.get( "AUT_ASSOCIATE" ) ).intValue() ]
         * ).charAt( 0 ) == '1', ( (String) values[ ( (Integer) indexes.get(
         * "AUT_DELETE" ) ).intValue() ] ).charAt( 0 ) == '1',
         * DBHelper.bigDecimalAsString( values[ ( (Integer) indexes.get(
         * "AUT_ID" ) ).intValue() ] ), convertObjectToDate(values[ ( (Integer)
         * indexes.get( "AUT_CREATINGDATE" ) ).intValue() ]),
         * DBHelper.bigDecimalAsString( values[ ( (Integer) indexes.get(
         * "AUT_CREATING_ELM_ID" ) ).intValue() ] ), convertObjectToDate(values[ (
         * (Integer) indexes.get( "AUT_MODIFYINGDATE" ) ).intValue() ]),
         * DBHelper.bigDecimalAsString( values[ ( (Integer) indexes.get(
         * "AUT_MODIFYING_ELM_ID" ) ).intValue() ] ) ); String pid =
         * DBHelper.bigDecimalAsString( values[ ( (Integer) indexes.get(
         * "AUX_PARENT_ELM_ID" ) ).intValue() ] ); String cid =
         * DBHelper.bigDecimalAsString( values[ ( (Integer) indexes.get(
         * "AUX_CHILD_ELM_ID" ) ).intValue() ] ); if( pid.equals( cid )) {
         * rights.setRoot( true ); } rightsList.add( rights ); } return
         * (OMSRights[]) rightsList.toArray( new OMSRights[ rightsList.size() ] );
         */
    }

    /* Converts the record set into OMSRights[] */
    static OMSRights[] records2omsRights(RecordSet recordSet, OMSElement[] containers) throws OMSMetaDataException, ParseException, OMSStructureException {
        Record[] records = recordSet.getRecords();
        HashMap indexes = buildIndexes(recordSet.getMetaData());
        OMSStructure structure = containers[0].getOMSStructure();
        for (int i = 0; i < containers.length; i++) {
            if (containers[i].getOMSStructure() != structure) {
                throw new OMSStructureException("Rights cannot be loaded for many elements from different structures at once");
            }
        }
        ArrayList rightsList = new ArrayList();
        for (int i = 0; i < records.length; i++) {
            Object[] values = records[i].getValues();
            // rights owner
            String oId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("AUT_SOURCE_ELM_ID")).intValue()]);
            OMSElement grantedTo = structure.getElementById(oId);
            if (grantedTo == null) {
                // continue; // TODO: really?
            }
            // elements the rights are granted for
            String eId = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("AUX_CHILD_ELM_ID")).intValue()]);
            OMSElement grantedFor = structure.getElementById(eId);
            // rights id
            OMSRights rights = new OMSRights(grantedFor, oId, ((String) values[((Integer) indexes.get("AUT_READ")).intValue()]).charAt(0) == '1', ((String) values[((Integer) indexes.get("AUT_WRITE")).intValue()]).charAt(0) == '1', ((String) values[((Integer) indexes.get("AUT_COMPOSE")).intValue()])
                    .charAt(0) == '1', ((String) values[((Integer) indexes.get("AUT_ASSOCIATE")).intValue()]).charAt(0) == '1', ((String) values[((Integer) indexes.get("AUT_DELETE")).intValue()]).charAt(0) == '1', DBHelper.bigDecimalAsString(values[((Integer) indexes.get("AUT_ID")).intValue()]),
                    convertObjectToDate(values[((Integer) indexes.get("AUT_CREATINGDATE")).intValue()]), DBHelper.bigDecimalAsString(values[((Integer) indexes.get("AUT_CREATING_ELM_ID")).intValue()]), convertObjectToDate(values[((Integer) indexes.get("AUT_MODIFYINGDATE")).intValue()]), DBHelper
                            .bigDecimalAsString(values[((Integer) indexes.get("AUT_MODIFYING_ELM_ID")).intValue()]));
            String pid = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("AUX_PARENT_ELM_ID")).intValue()]);
            String cid = DBHelper.bigDecimalAsString(values[((Integer) indexes.get("AUX_CHILD_ELM_ID")).intValue()]);
            if (pid.equals(cid)) {
                rights.setRoot(true);
            }
            rightsList.add(rights);
        }
        return (OMSRights[]) rightsList.toArray(new OMSRights[rightsList.size()]);
    }

    private static int getLevel(String path) {
        int level = 0;
        int index = 0;
        while (path != null && (index = path.indexOf(PATH_SEPARATOR, index)) != -1) {
            index += PATH_SEPARATOR.length();
            level++;
        }
        return level;
    }

    private static HashMap<String, Integer> buildIndexes( RecordSetMetaData metaData ) 
    {
        HashMap<String, Integer> result = new HashMap<String, Integer>();
        Column[] columns = metaData.getColumns();
        for( int i = 0; columns != null && i < columns.length; i++ ) 
        {
            result.put( columns[ i ].getName(), new Integer( i ) );
        }

        return result;
    }
    
    public static void loadSimplevalues( OMSStructure tree, RecordSet recordSet, OMSMetaData metaData )
    throws ParseException, OMSMetaDataException
    {
    	loadValues( tree, recordSet, metaData, Datatype.TYPE_SIMPLE );
    }
    
    public static void loadUniquevalues( OMSStructure tree, RecordSet recordSet, OMSMetaData metaData )
    throws ParseException, OMSMetaDataException
    {
    	loadValues( tree, recordSet, metaData, Datatype.TYPE_UNIQUE );
    }

    public static void loadClobvalues( OMSStructure tree, RecordSet recordSet, OMSMetaData metaData )
    throws ParseException, OMSMetaDataException
    {
    	loadValues( tree, recordSet, metaData, Datatype.TYPE_CLOB );
    }

    public static void loadBlobvalues( OMSStructure tree, RecordSet recordSet, OMSMetaData metaData )
    throws ParseException, OMSMetaDataException
    {
    	loadValues( tree, recordSet, metaData, Datatype.TYPE_BLOB );
    }
    
    public static void loadElements( OMSStructure tree, RecordSet recordSet, OMSMetaData metaData )
    throws ParseException, OMSMetaDataException
    {
        logger.debug( "Start loading elements into oms structure" );
        Record[] records = recordSet.getRecords();
        logger.debug( "Recordset size: " + records.length );
    	HashMap<String, Integer> indexes = buildIndexes( recordSet.getMetaData() );
        // test if there is element data in record set
        int invalid = 0;
        int duplicated = 0; 
        if( indexes.get( "ELM_ID" ) != null ) 
        {
	        for( int i = 0; i < records.length; i++ ) 
	        {
	            if( i != 0 && i % 5000 == 0 ) 
	            {
	                logger.debug( i + " of " + records.length + " records converted into elements" );
	            }
	            Object[] values = records[ i ].getValues();
	            String elmId = DBHelper.bigDecimalAsString( values[ indexes.get( "ELM_ID" ).intValue() ] );
	            if( tree.getElementById( elmId ) == null ) 
	            {
	                String elmDisplayName = (String) values[ indexes.get( "ELM_DISPLAYNAME" ).intValue() ];
	                String elmBookmark = (String) values[ indexes.get( "ELM_BOOKMARK" ).intValue() ];
	                String elmSchemeId = DBHelper.bigDecimalAsString( values[ indexes.get( "ELM_SCM_ID" ).intValue() ] );
	                // int elmValid = DBHelper.bigDecimalAsInt( values[ ( (Integer)
	                // indexes.get( "ELM_VALID" ) ).intValue() ] );
	                Date elmCreated = indexes.get( "ELM_CREATINGDATE" ) != null ? convertObjectToDate( values[ indexes.get( "ELM_CREATINGDATE" ).intValue() ] ) : null;
	                String elmCreater = indexes.get( "ELM_CREATING_ELM_ID" ) != null ? DBHelper.bigDecimalAsString( values[ indexes.get( "ELM_CREATING_ELM_ID" ).intValue() ] ) : null;
	                Date elmModified = indexes.get( "ELM_MODIFYINGDATE" ) != null ? convertObjectToDate( values[ indexes.get( "ELM_MODIFYINGDATE" ).intValue() ] ) : null;
	                String elmModifier = indexes.get( "ELM_MODIFYING_ELM_ID" ) != null ? DBHelper.bigDecimalAsString( values[ indexes.get( "ELM_MODIFYING_ELM_ID" ).intValue() ] ) : null;
	                // append the element
	                tree.createAndAppendElement( null, metaData.getSchemeById( elmSchemeId ).getNamespace().getPath(), metaData.getSchemeById( elmSchemeId ).getName(), elmId, elmDisplayName, elmBookmark, elmCreated, elmCreater, elmModified, elmModifier );
	            }
	            else
	            {
	            	duplicated++;
	            }
	            
	            // clean element
	            records[ i ].setValue( indexes.get( "ELM_ID" ).intValue(), null);
	            records[ i ].setValue( indexes.get( "ELM_DISPLAYNAME" ).intValue(), null);
	            records[ i ].setValue( indexes.get( "ELM_BOOKMARK" ).intValue(), null);
	            records[ i ].setValue( indexes.get( "ELM_SCM_ID" ).intValue(), null);
	        }
        }
        else
        {
        	invalid++;
        }
        logger.debug( "Invalid records count: " + invalid );
        logger.debug( "Duplicated records count: " + duplicated );
        logger.debug( "Loading elements into oms structure finished" );
    }
    
    public static void loadCompositions( OMSStructure tree, RecordSet recordSet, OMSMetaData metaData )
    throws OMSStructureException, OMSMetaDataException, ParseException
    {
        logger.debug( "Start loading compositions into oms structure" );
        Record[] records = recordSet.getRecords();
        logger.debug( "Recordset size: " + records.length );
    	HashMap<String, Integer> indexes = buildIndexes( recordSet.getMetaData() );
        HashSet<String> addedIds = new HashSet<String>( records.length );
        // test if there is composition data in record set
        int invalid = 0;
        int duplicated = 0; 
        if( indexes.get( "CMP_ID" ) != null ) 
        {
	        for( int i = 0; i < records.length; i++ ) 
	        {
	            if( i != 0 && i % 5000 == 0 ) 
	            {
	                logger.debug( i + " of " + records.length + " records converted into compositions" );
	            }
	            Object[] values = records[ i ].getValues();
                String cmpId = DBHelper.bigDecimalAsString( values[ indexes.get( "CMP_ID" ).intValue() ] );
                if( cmpId != null && !addedIds.contains( cmpId ) ) 
                {
                    String cmpTargetId = DBHelper.bigDecimalAsString( values[ indexes.get( "CMP_TARGET_ELM_ID" ).intValue() ] );
                    String cmpSourceId = DBHelper.bigDecimalAsString( values[ indexes.get( "CMP_SOURCE_ELM_ID" ).intValue() ] );
                    String cmpAttributeId = DBHelper.bigDecimalAsString( values[ indexes.get( "CMP_CAT_ID" ).intValue()] );
                    int cmpOrder = indexes.get( "CMP_SIBLING_ORDER" ) != null ? DBHelper.bigDecimalAsInt( values[ indexes.get( "CMP_SIBLING_ORDER" ).intValue() ] ) : 0;
                    String cmpKey = indexes.get( "CMP_KEY" ) != null ? (String) values[ indexes.get( "CMP_KEY" ).intValue() ] : null;
                    String cmpPath = (String) values[ indexes.get( "CMP_PATH" ).intValue() ];
                    Date cmpCreated = indexes.get( "CMP_CREATINGDATE" ) != null ? convertObjectToDate( values[ indexes.get("CMP_CREATINGDATE" ).intValue() ] ) : null;
                    String cmpCreater = indexes.get( "CMP_CREATING_ELM_ID" ) != null ? DBHelper.bigDecimalAsString( values[ indexes.get("CMP_CREATING_ELM_ID" ).intValue() ] ) : null;
                    Date cmpModified = indexes.get( "CMP_MODIFYINGDATE" ) != null ? convertObjectToDate( values[ indexes.get("CMP_MODIFYINGDATE" ).intValue() ] ) : null;
                    String cmpModifier = indexes.get( "CMP_MODIFYING_ELM_ID" ) != null ? DBHelper.bigDecimalAsString( values[ indexes.get( "CMP_MODIFYING_ELM_ID" ).intValue() ] ) : null;
                    // append composition
                    OMSElement source = (OMSElement) tree.getElementById( cmpSourceId );
                    OMSElement target = (OMSElement) tree.getElementById( cmpTargetId );
                    target.setPath( cmpPath );
                    // String catName = null;
                    // boolean isComposition = false;
                    ComplexAttribute cat = metaData.getComplexAttributeById( cmpAttributeId );
                    if( source == null ) 
                    {
                        source = tree.getRootElement();
                    }
                    String catName = cmpSourceId == null ? OMSStructure.OBJECTS_ATTRIBUTE : cat.getName();
                    source.createAndAppendRelation( cmpSourceId, target, catName,cmpAttributeId, true, cmpId, cmpKey, null, cmpOrder, cmpCreater, cmpCreated, cmpModifier, cmpModified );
                    addedIds.add( cmpId );
                }
	            else if( addedIds.contains( cmpId ) )
	            {
	            	duplicated++;
	            }
                // clean
                records[ i ].setValue( indexes.get( "CMP_ID" ).intValue(), null);
                records[ i ].setValue( indexes.get( "CMP_TARGET_ELM_ID" ).intValue(), null);
                records[ i ].setValue( indexes.get( "CMP_SOURCE_ELM_ID" ).intValue(), null);
                records[ i ].setValue( indexes.get( "CMP_CAT_ID" ).intValue(), null);
	        }
        }
        else
        {
        	invalid++;
        }
        logger.debug( "Invalid records count: " + invalid );
        logger.debug( "Duplicated records count: " + duplicated );
        logger.debug( "Loading compositions into oms structure finished" );
    }
    
    public static void loadAssociations( OMSStructure tree, RecordSet recordSet, OMSMetaData metaData )
    throws OMSStructureException, OMSMetaDataException, ParseException
    {
        logger.debug( "Start loading associations into oms structure" );
        Record[] records = recordSet.getRecords();
        logger.debug( "Recordset size: " + records.length );
    	HashMap<String, Integer> indexes = buildIndexes( recordSet.getMetaData() );
        HashSet<String> addedIds = new HashSet<String>( records.length );
        // test if there is association data in record set
        int invalid = 0;
        int duplicated = 0; 
        if( indexes.get( "ASS_ID" ) != null ) 
        {
	        for( int i = 0; i < records.length; i++ ) 
	        {
	            if( i != 0 && i % 5000 == 0 ) 
	            {
	                logger.debug( i + " of " + records.length + " records converted into associations" );
	            }
	            Object[] values = records[i].getValues();
	            String assId = DBHelper.bigDecimalAsString( values[ indexes.get( "ASS_ID" ).intValue() ] );
	            if( assId != null && !addedIds.contains( assId ) ) 
	            {
	                String assSourceId = DBHelper.bigDecimalAsString( values[ indexes.get( "ASS_SOURCE_ELM_ID" ).intValue() ] );
	                String assTargetId = DBHelper.bigDecimalAsString( values[ indexes.get( "ASS_TARGET_ELM_ID" ).intValue() ] );
	                String assAttributeId = DBHelper.bigDecimalAsString( values[ indexes.get( "ASS_CAT_ID" ).intValue() ] );
	                int assOrder = indexes.get( "ASS_SIBLING_ORDER" ) != null ? DBHelper.bigDecimalAsInt( values[ indexes.get( "ASS_SIBLING_ORDER" ).intValue() ] ) : 0;
	                String assKey = indexes.get( "ASS_KEY" ) != null ? (String) values[ indexes.get( "ASS_KEY" ).intValue() ] : null;
	                Date assCreated = indexes.get( "ASS_CREATINGDATE" ) != null ? convertObjectToDate( values[ indexes.get( "ASS_CREATINGDATE" ).intValue() ] ) : null;
	                String assCreater = indexes.get( "ASS_CREATING_ELM_ID" ) != null ? DBHelper.bigDecimalAsString( values[ indexes.get( "ASS_CREATING_ELM_ID" ).intValue() ] ) : null;
	                Date assModified = indexes.get( "ASS_MODIFYINGDATE" ) != null ? convertObjectToDate( values[ indexes.get( "ASS_MODIFYINGDATE" ).intValue() ] ) : null;
	                String assModifier = indexes.get( "ASS_MODIFYING_ELM_ID" ) != null ? DBHelper.bigDecimalAsString( values[ indexes.get( "ASS_MODIFYING_ELM_ID" ).intValue() ] ) : null;
	                // append association
	                OMSElement source = (OMSElement) tree.getElementById( assSourceId );
	                OMSElement target = (OMSElement) tree.getElementById( assTargetId );
	                if( source != null && target != null ) 
	                {
	                    source.createAndAppendRelation( assSourceId, target, metaData.getComplexAttributeById( assAttributeId ).getName(),assAttributeId, false, assId, assKey, null, assOrder, assCreater, assCreated, assModifier, assModified );
	                }
	                // add it anyway - further processing makes no sense
                    addedIds.add( assId );
	            }
	            else if( addedIds.contains( assId ) )
	            {
	            	duplicated++;
	            }
	            // clean
	            records[ i ].setValue( indexes.get( "ASS_ID" ).intValue(), null);
	            records[ i ].setValue( indexes.get( "ASS_SOURCE_ELM_ID" ).intValue(), null);
	            records[ i ].setValue( indexes.get( "ASS_TARGET_ELM_ID" ).intValue(), null);
	            records[ i ].setValue( indexes.get( "ASS_CAT_ID" ).intValue(), null);
	        }
        }
        else
        {
        	invalid++;
        }
        logger.debug( "Invalid records count: " + invalid );
        logger.debug( "Duplicated records count: " + duplicated );
        logger.debug( "Loading associations into oms structure finished" );
    }

    private static void loadValues( OMSStructure tree, RecordSet recordSet, OMSMetaData metaData, char valueType )
    throws ParseException, OMSMetaDataException
    {
    	String typeString = null;
    	String typeLetter = Character.toString( valueType ).toUpperCase();
    	switch( valueType )
    	{
    		case Datatype.TYPE_BLOB:
    			typeString = "blob";
    			break;
    		case Datatype.TYPE_CLOB:
    			typeString = "clob";
    			break;
    		case Datatype.TYPE_SIMPLE:
    			typeString = "simple";
    			break;
    		case Datatype.TYPE_UNIQUE:
    			typeString = "unique";
    			break;
    		default:
    			throw new OMSMetaDataException( "Invalid value type: '" + valueType + "'" );
    	}
    		
        logger.debug( "Start loading " + typeString + " values into oms structure" );
        Record[] records = recordSet.getRecords();
        logger.debug( "Recordset size: " + records.length );
    	HashMap<String, Integer> indexes = buildIndexes( recordSet.getMetaData() );
        HashSet<String> addedIds = new HashSet<String>( records.length );
        // test if there is value data in record set
        int invalid = 0;
        int duplicated = 0; 
        if( indexes.get( typeLetter + "VL_ID" ) != null ) 
        {
	        for( int i = 0; i < records.length; i++ ) 
	        {
	            if( i != 0 && i % 5000 == 0 ) 
	            {
	                logger.debug( i + " of " + records.length + " records converted into " + typeString + " values" );
	            }
	            Object[] values = records[i].getValues();
	            String vId = DBHelper.bigDecimalAsString( values[ indexes.get( typeLetter + "VL_ID" ).intValue() ] );
	            if( vId != null && !addedIds.contains( vId ) ) 
	            {
		        	String vElmId = DBHelper.bigDecimalAsString( values[ indexes.get( typeLetter + "VL_ELM_ID" ).intValue() ] );
		        	OMSElement element = tree.getElementById( vElmId );
		        	if( element != null )
		        	{
		                Object vValue = values[ indexes.get( typeLetter + "VL_VALUE" ).intValue() ];
		                //String vValueType = (String) values[ indexes.get( typeLetter + "VL_VALUETYPE" ).intValue() ];
		                //String vDatId = DBHelper.bigDecimalAsString( values[ indexes.get( typeLetter + "VL_DAT_ID" ).intValue() ] );
		                String vDenId = DBHelper.bigDecimalAsString( values[ indexes.get( typeLetter + "VL_DEN_ID" ).intValue() ] );
		                String vSatId = DBHelper.bigDecimalAsString(values[ indexes.get( typeLetter + "VL_SAT_ID" ).intValue() ] );
		                Date vCreated = indexes.get( typeLetter + "VL_CREATINGDATE" ) != null ? convertObjectToDate( values[ indexes.get( typeLetter + "VL_CREATINGDATE" ).intValue() ] ) : null;
		                String vCreater = indexes.get( typeLetter + "VL_CREATING_ELM_ID" ) != null ? DBHelper.bigDecimalAsString( values[ indexes.get( typeLetter + "VL_CREATING_ELM_ID" ).intValue() ] ) : null;
		                Date vModified = indexes.get( typeLetter + "VL_MODIFYINGDATE" ) != null ? convertObjectToDate( values[ indexes.get( typeLetter + "VL_MODIFYINGDATE" ).intValue() ] ) : null;
		                String vModifier = indexes.get( typeLetter + "VL_MODIFYING_ELM_ID" ) != null ? DBHelper.bigDecimalAsString( values[ indexes.get( typeLetter + "VL_MODIFYING_ELM_ID" ).intValue() ] ) : null;
		                // append the simple value
		                element.createAndAppendValue( metaData.getDatatypeEnumerationById( vDenId ).getName(), metaData.getSimpleAttributeById( vSatId ).getName(), vId, vValue, vCreated, vCreater, vModified, vModifier );
		        	}
		        	// even when no element found, processing this value againa has no sense
	                addedIds.add( vId );
		        }
	            else if( addedIds.contains( vId ) )
	            {
	            	duplicated++;
	            }
	            // clean
	            records[ i ].setValue( indexes.get( typeLetter + "VL_ID" ).intValue(), null );
	            records[ i ].setValue( indexes.get( typeLetter + "VL_VALUE" ).intValue(), null );
	            records[ i ].setValue( indexes.get( typeLetter + "VL_ELM_ID" ).intValue(), null );
	            records[ i ].setValue( indexes.get( typeLetter + "VL_DEN_ID" ).intValue(), null );
	            records[ i ].setValue( indexes.get( typeLetter + "VL_SAT_ID" ).intValue(), null );
	        }
        }
        else
        {
        	invalid++;
        }
        logger.debug( "Invalid records count: " + invalid );
        logger.debug( "Duplicated records count: " + duplicated );
        logger.debug( "Loading " + typeString + " values into oms structure finished" );
    }
}
